The present invention relates to database systems, and more specifically, to the processing of database tables having NULLS.
Relational database management systems require that a marker be present to indicate that a data value does not exist or is inapplicable. In Structured Query Language (SQL) this marker is called a NULL. An example of a missing value may occur when a table in a database stores customer-related information like the first name, last name, and address of each customer. Some customers may additionally have a middle name or middle initial—while others may not. For these cases, using NULLs can be a way to express that a customer does not have a middle initial. Other examples for unknown values could be missing phone/fax numbers or email addresses.
Besides unknown information, another typical situation where NULLs are used is if a value is not even applicable. For example, the date when someone was on maternity leave is applicable for females, but not for males.
NULLs are not considered to be values of the domain of the data, i.e. NULL is not a string or integer. The introduction of such NULLs leads to so-called three-valued logic. The reason is that only values in the same (or a compatible) domain can be compared with each other and give a well-defined result. The comparison is either “true” or “false”. However, comparing a value with NULL always results in a third value, “unknown”. For example, comparing “1=1” is “true”; “10<9” is “false”; but “1=NULL” is “unknown” because the actual value for which NULL was used is unknown (or not applicable).
Current database systems are the primary exploiters and implementers of three-valued logic. One can write a query like the following:
SELECT *
FROM customers
WHERE phone_number=‘1-800-123-4567’
This query finds all customers with that particular phone number. All customers with other phone numbers (or with an “unknown” phone number) are excluded from the result set.